/*
 AMENDMENTS:
 DATE          	NAME        VERSION     DESCRIPTION
--------       	-----       -------     -----------
 30/10/2012    	MKANG       1.0.0       1.Create New.
*/
package com.mbbmap.app.home;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.mbbmap.app.dao.UserDao;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.app.manager.SystemLogManager;
import com.mbbmap.security.dao.SecUserDao;
import com.mbbmap.util.Constants;
import com.mbbmap.util.DateUtil;
import com.mbbmap.util.EnhancedException;

public class SecUsersHome {
	private static SecUsersHome instance = null;

	/**Returns the single instance, creating one if it's the
	* first time this method is called.
	* 
	*
	* @return IslamicContractHome. The single instance.
	*/
	public static SecUsersHome getInstance() {
		//thread-safe implementation
		if (instance == null) {
			synchronized (SecUsersHome.class) {
				if (instance == null) {
					instance = new SecUsersHome();
				}
			}
		}
		return instance;
	}

	
	private SecUsersHome() {
		super();
	}
	
	
	/**Get user
	 * 
	 * @param merchant
	 * @param loginId
	 * @return
	 */
	public UserDao getUser (String merchant, String loginId) {
        Connection connection = null;
        PreparedStatement pstmtQuery = null;
        SystemLogManager sysLogMgr = SystemLogManager.getInstance();;
        DBAccessHome dbh = DBAccessHome.getInstance();
        UserDao user = new UserDao();

        System.out.println("User edit begin..");
        System.out.println(merchant);
        System.out.println(loginId);
        String selectSql =  "SELECT A.ID, A.LOGIN_ID, A.NAME, A.ROLE_CODE, A.ENABLED, A.LOCKED, A.REMARKS, A.NO_ACCESS_DATE, B.ROLE_NAME  FROM "
                + Constants.DB_TBL_USER + " A, " + Constants.DB_TBL_ACCESSROLE + " B WHERE A.MER_NAME = ? AND A.LOGIN_ID= ? AND A.ROLE_CODE = B.ROLE_CODE ";
        try {
            connection = dbh.getDBConnection();
            //PropertyHome ph = PropertyHome.getInstance();

            pstmtQuery =  connection.prepareStatement(selectSql);
            pstmtQuery.setString(1, merchant);
            pstmtQuery.setString(2, loginId);
            System.out.println("The selectSql string is "+selectSql);
            
            ResultSet rs = pstmtQuery.executeQuery();
            if (rs.next()) {
                user.setUserDBId(rs.getLong(1));
                user.setUserLogin(rs.getString(2));
                user.setUserName(rs.getString(3));
//                user.setAgencyCode(rs.getString(4));
                user.setUserRole(rs.getString(4));
                user.setUserEnabled(rs.getString(5));

                if (rs.getString(6).equals(Constants.DB_YES)) {
                    user.setUserLocked(true);
                } else {
                    user.setUserLocked(false);
                }

                user.setRemark(rs.getString(7));

                java.util.Date noAccessDate = rs.getDate(8);
                if (noAccessDate == null ) {
                    user.setBlockAccessDate("");
                } else {
                    user.setBlockAccessDate(DateUtil.formatLocalSlash(noAccessDate));
                }

                user.setRoleName(rs.getString(9));
//                user.setCountryCode(rs.getString(11));

                // get email and contact No
                //user.setEmail(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_EMAIL));
                //user.setHandPhoneNo(ph.getProperty(Long.toString(user.getUserDBId()), user, UserDao.PROP_HANDPHONE_NO));

            }
			pstmtQuery.close();
            rs.close();
			pstmtQuery=null;
			rs=null;

        } catch (java.sql.SQLException sqle) {
            sysLogMgr.logMsg("Error in SecUsersHome.getUsers() SQL execution : sql=" + selectSql + "\n " + sqle);
            sqle.printStackTrace();
        } finally {
			if (pstmtQuery != null) {
				try {
				    pstmtQuery.close();
				} catch (SQLException e1) {
				}
			}
			if (connection !=null)
			    dbh.freeDBConnection (connection);
		}

	    return user;

	}
	
	
	/**This is to get Security Group Users
	 * 	
	 * 	Author 	: 	Michael Kang
	 *	Date	:	13/02/2009
	 *
	 */
	public ArrayList findSecGroupUserList(String strMerchant, String strSelectedGroup) {
		////System.out.println("********************"+newIcNo);

		ArrayList secGroupLevelList = new ArrayList();
		ArrayList secGroupUserList = new ArrayList();

		int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		DBConnectionManager connMgr;
		String connectionPool;
		SystemLogManager sysLogMgr;

		connectionPool = ConfigManager.getInstance().getConnectionPool();
		connMgr = DBConnectionManager.getInstance();
		String timeOutValue = ConfigManager.getInstance().get(Constants.DB_WAIT_TIME);
		if (timeOutValue != null) {
			try {
				TIMEOUT = Integer.parseInt(timeOutValue);
			} catch (NumberFormatException e) {}
		}
		sysLogMgr = SystemLogManager.getInstance();

		Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);

		PreparedStatement stmt = null;
		ResultSet rs= null;
		PreparedStatement stmt2 = null;
		ResultSet rs2= null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");

		try {
			if (conn == null) {
				EnhancedException ex = new EnhancedException("");
				ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_001 [Connection at : " + connectionPool + "]");
				ex.setExtraMessage("Unable to get connection. See DBConnectionManager.log");
				sysLogMgr.logError(ex);
			} //if (conn == null)
			else{
				StringBuffer sqlStmt2 = new StringBuffer("");
				sqlStmt2.append("SELECT * FROM " + Constants.DB_TBL_USER + "  WHERE MER_NAME = '" + strMerchant + "' AND ROLE_CODE='"+strSelectedGroup+"' ORDER BY NAME ASC");
				//System.out.println("SecUsersHome_findSecGroupUserList_003 : " + sqlStmt2);

				stmt2 = conn.prepareStatement(sqlStmt2.toString());

				rs2 = stmt2.executeQuery();
				////System.out.println("Resultset . next()");
				String sLoginID = "";
				String sUserName = "";
				String sFullName = "";
				String sEmail = "";
				String sDept = "";
				String sCountryCode = "";
				String sLocked = "";
				Date dLastLogin = null;
				int dormantdays = 0;
				while (rs2.next()){
					SecUserDao secuserdao = new SecUserDao();

					sLoginID = "";
					sLoginID = rs2.getString("LOGIN_ID");
					if (sLoginID != null){
						sLoginID = sLoginID.trim();
					}

					sUserName = "";
					sUserName = rs2.getString("NAME");
					if (sUserName != null){
						sUserName = sUserName.trim();
					}
					else
					{
						sUserName = "";
					}
					
					sFullName = "";
					sFullName = rs2.getString("FULL_NAME");
					if (sFullName != null){
						sFullName = sFullName.trim();
					}
					else
					{
						sFullName = "";
					}
					
					sEmail = "";
					sEmail = rs2.getString("EMAIL");
					if (sEmail != null){
						sEmail = sEmail.trim();
					}
					else
					{
						sEmail = "";
					}
					
					sDept = "";
					sDept = rs2.getString("DEPT");
					if (sDept != null){
						sDept = sDept.trim();
					}
					else
					{
						sDept = "";
					}
					
					sCountryCode = "";
					sCountryCode = rs2.getString("COUNTRY_CODE");
					if (sCountryCode != null){
						sCountryCode = sCountryCode.trim();
					}
					else
					{
						sCountryCode = "";
					}
					
					sLocked = "";
					sLocked = rs2.getString("LOCKED");
					if (sLocked != null){
						sLocked = sLocked.trim();
					}
					else
					{
						sLocked = "";
					}
					
					dLastLogin = rs2.getTimestamp("LAST_LOGIN_TIMESTAMP");
					if (dLastLogin != null){
						Date sysdate = new Date();
						long startDateTime = dLastLogin.getTime();
					    long endDateTime = sysdate.getTime();
					    long milPerDay = 1000*60*60*24; 
					    
					    dormantdays = (int) ((endDateTime - startDateTime) / milPerDay); 
					} else {
						dormantdays = 0;
					}
					
					//System.out.println("SecUsersHome_findSecGroupUserList_003 : sLoginID " + sLoginID);
					secuserdao.set(sLoginID,strSelectedGroup,"", "");
					secuserdao.setUserName(sUserName);
					secuserdao.setFullName(sFullName);
					secuserdao.setEmail(sEmail);
					secuserdao.setCountryCode(sCountryCode);
					secuserdao.setLocked(sLocked);
					secuserdao.setDept(sDept);
					secuserdao.setDormantDays(dormantdays);
					secGroupUserList.add(secuserdao);
				}
				stmt2.close();
				rs2.close();
				stmt2=null;
				rs2=null;

			} //[ELSE] if (conn == null)
		} catch (Exception e) {
			EnhancedException ex = new EnhancedException(e.toString());
			ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_002 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Exception Encountered");
			sysLogMgr.logError(ex);
		} catch (Throwable t) {
			EnhancedException ex = new EnhancedException(t.toString());
			ex.setExceptionLocation("SecUsersHome_findSecGroupUserList_003 [Connection at : " + connectionPool + "]");
			ex.setExtraMessage("Throwable Encountered");
			sysLogMgr.logError(ex);
		} finally {
			if (stmt2 != null) {
				try {
					stmt2.close();
				} catch (SQLException e1) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}

				////System.out.println("Resultset12 . next()");
		return secGroupUserList;
	}
}//end class